package com.example.AIstudy.dao;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.AIstudy.domain.LevelRecord;
import org.apache.ibatis.annotations.*;

import java.sql.Time;
import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface LevelRecordDao extends BaseMapper<LevelRecord> {


    //新增一个学生的学习周记录
    @Insert("INSERT INTO levelrecord(startTime,endTime,level,evaluation,stuID) " +
            "VALUES (#{startTime},#{endTime},#{level},#{evaluation},#{stuID})")
    @SelectKey(statement = "select last_insert_id()", keyProperty = "id",
            keyColumn = "id", resultType = Integer.class, before = false)
    void insertLevelRecord(LevelRecord levelRecord);

    //分页获取学生的学习记录
    @Select("SELECT * FROM levelrecord ${ew.customSqlSegment}")
    public IPage<LevelRecord> findRecords(IPage<LevelRecord> page,
                                        @Param(Constants.WRAPPER) QueryWrapper<LevelRecord> wrapper);

//    @Select("SELECT Avg(stuScore/score),category FROM cuotiji WHERE cuotiji.stuID=#{stuID} GROUP BY category ORDER BY category")
//    public List<Float> getCorrectRateIncuoti(Integer stuID);
@Select("SELECT IFNULL(AVG(IFNULL(cuotiji.stuScore, 0) / IFNULL(cuotiji.score, 1)), 0) AS avg_score\n" +
        "FROM (\n" +
        "            SELECT 'fanyi' AS category\n" +
        "            UNION ALL\n" +
        "            SELECT 'xuanze'\n" +
        "            UNION ALL\n" +
        "            SELECT 'yuedu'\n" +
        "            UNION ALL\n" +
        "           SELECT 'zuowen'\n" +
        "        ) AS categories\n" +
        "        LEFT JOIN cuotiji ON cuotiji.category = categories.category AND cuotiji.stuID = #{stuID}\n" +
        "                  AND cuotiji.insertTime >= #{startTime} \n" +
        "                  AND cuotiji.insertTime <= #{endTime} \n" +
        "        GROUP BY categories.category")
public List<Float> getCorrectRateIncuoti(Integer stuID, Timestamp startTime, Timestamp endTime);

//    @Select("SELECT Avg(redoScore/score),category FROM cuotiji,redorecord  " +
//            "where cuotiji.id=redorecord.cuotiID and cuotiji.stuID=#{stuID} GROUP BY category ORDER BY category")
//    public List<Float> getCorrectRateInRedo(Integer stuID);

    //修改版
    @Select("SELECT IFNULL(AVG(IFNULL(redorecord.redoScore, 0) / IFNULL(cuotiji.score, 1)), 0) AS avg_score " +
            "FROM (\n" +
            "    SELECT 'fanyi' AS category\n" +
            "    UNION ALL\n" +
            "    SELECT 'xuanze'\n" +
            "    UNION ALL\n" +
            "    SELECT 'yuedu'\n" +
            "    UNION ALL\n" +
            "    SELECT 'zuowen'\n" +
            ") AS categories\n" +
            "LEFT JOIN cuotiji ON cuotiji.category = categories.category AND cuotiji.stuID = #{stuID}\n" +
            "LEFT JOIN redorecord ON cuotiji.id = redorecord.cuotiID and redoTime<=#{endTime} and redoTime>=#{startTime} \n" +
            "GROUP BY categories.category\n" +
            "ORDER BY categories.category;")
    public List<Float> getCorrectRateInRedo(Integer stuID, Timestamp startTime, Timestamp endTime);
    @Select("SELECT \n" +
            "    CASE \n" +
            "        WHEN IFNULL(AVG(IFNULL(recorddetail.stuScore, 0) / IFNULL(exercise.score, 1)), 0)=0.00000000 THEN 0\n" +
            "        ELSE IFNULL(AVG(IFNULL(recorddetail.stuScore, 0) / IFNULL(exercise.score, 1)), 0)\n" +
            "    END AS avg_score " +
            "FROM (\n" +
            "    SELECT 'fanyi' AS category\n" +
            "    UNION ALL\n" +
            "    SELECT 'xuanze'\n" +
            "    UNION ALL\n" +
            "    SELECT 'yuedu'\n" +
            "    UNION ALL\n" +
            "    SELECT 'zuowen'\n" +
            ") AS categories\n" +
            "LEFT JOIN exercise ON exercise.category = categories.category\n" +
            "LEFT JOIN recorddetail ON exercise.id = recorddetail.exerciseID and recorddetail.stuID=#{stuID}\n" +
            "and recorddetail.insertTime<=#{endTime} and recorddetail.insertTime>=#{startTime} " +
            "GROUP BY categories.category\n" +
            "ORDER BY categories.category;")
    public List<Float> getCorrectRateInExe(Integer stuID, Timestamp startTime, Timestamp endTime);

    @Select("SELECT * FROM levelrecord WHERE stuID=#{stuID} ORDER BY startTime DESC LIMIT 6")
    public List<LevelRecord> getRecordByTime(Integer stuID);
}
